import logging
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import openpyxl
import datetime
import math
import os
import sys
import configparser
import ChengBanBuMen
import ReplaceBuMen
from hanchlogger import logger
import hanchxl

import BuMenChengJi
from tkinter import simpledialog

from openpyxl.cell import MergedCell
timeCur = datetime.datetime.now()

numYear = 2024
numMonth = 12

# dirPath = 'E:\\日常工作\\接诉即办排名情况\\2024\\第{}季度\\{}月'.format(math.ceil(numMonth / 3), numMonth)
dirPath = 'E:\\日常工作\\接诉即办排名情况\\2024\\{}月'.format(numMonth)
# dirPath = 'E:\\日常工作\\接诉即办排名情况\\2023\\第二季度\\5月'
# dirPath = 'E:\\日常工作\\11月\\14\\每日必办\\得分统计表\\{}月'.format(numMonth)
# dirPath = os.path.dirname(os.path.realpath(sys.executable))

logFile = dirPath + '\\JieDaoPaiMing.log'
logger.get_log(logFile, 'LoggerJieDaoPaiMing', logging.DEBUG)

iniFilePathCur = dirPath + '\\autoUnresolvedList.ini'
iniConfig = configparser.ConfigParser(allow_no_value=True)
iniConfig.read(iniFilePathCur, encoding='utf-8')

fileJieDan = dirPath + '\\工单明细表-接单.xlsx'
fileKaoHe = dirPath + '\\工单明细表-考核.xlsx'
fileKaoHePiPei = dirPath + '\\工单明细表-考核-已匹配承办部门.xlsx'
fileJieDanPiPei = dirPath + '\\工单明细表-接单-已匹配承办部门.xlsx'
fileJieDanNo = dirPath + '\\工单明细表-接单-未处理.xlsx'
fileKaoHeNo = dirPath + '\\工单明细表-考核-未处理.xlsx'
fileChengBanBuMen = dirPath + '\\承办部门.xlsx'
fileResultSave = dirPath + '\\拱辰街道{}月考核期接诉即办月度排名.xlsx'.format(numMonth)
fileResultMuBan = dirPath + '\\模板-月度得分统计.xlsx'
fileColIndexInfo = dirPath + '\\数据列索引信息.xlsx'
fileChengBanReplace = dirPath + '\\需替换部门.xlsx'
fileQuanBu = dirPath + '\\全部工单.xlsx'

# 打开工单明细表-考核.xlsx
bookKaoHe = openpyxl.load_workbook(fileKaoHe, data_only=True)
sheetKaoHe = bookKaoHe.active
# sheetKaoHe = bookKaoHe['考核工单']
maxRowKaoHe = sheetKaoHe.max_row
maxColKaoHe = sheetKaoHe.max_column
logger.debug('工单明细表-考核.xlsx -------行数：{}行    列数：{}列'.format(maxRowKaoHe, maxColKaoHe))

# 打开 工单明细表-接单.xlsx
bookJieDan = openpyxl.load_workbook(fileJieDan, data_only=True)
sheetJieDan = bookJieDan.active
# sheetJieDan = bookJieDan['接单情况']
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('工单明细表-接单.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))

# 打开替换部门字典表
logger.debug('打开替换部门字典表 文件：{}'.format(fileChengBanReplace))
replaceBuMen = ReplaceBuMen.ReplaceDirectory()
replaceBuMen.load_directory(fileChengBanReplace)
listReplaceBuMen = replaceBuMen.get_replace_directory()

# 打开 全部工单.xlsx
bookQuanBu = openpyxl.load_workbook(fileQuanBu, data_only=True)
sheetQuanBu = bookQuanBu.active
maxRowQuanBu = sheetQuanBu.max_row
maxColQuanBu = sheetQuanBu.max_column
logger.debug('全部工单.xlsx -------行数：{}行    列数：{}列'.format(maxRowQuanBu, maxColQuanBu))

colCode = hanchxl.get_col_index(sheetJieDan, '工单编号')
sheetJieDan.insert_cols(colCode + 1, 3)
sheetJieDan.cell(1, colCode + 1).value = '承办部门'
sheetJieDan.cell(1, colCode + 2).value = '参与部门'
sheetJieDan.cell(1, colCode + 3).value = '是否处理'

hanchxl.pi_pei_te_ding_col(sheetJieDan, '工单编号', sheetQuanBu, '承办部门', '参与部门')
for buMenInfo in listReplaceBuMen:
    if buMenInfo.isAll is True:
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetKaoHe, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDan, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDan, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
    else:
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
hanchxl.remove_repeat_value(sheetJieDan, '承办部门', '参与部门')

# 打开 数据列索引信息.xlsx
bookColIndex = openpyxl.load_workbook(fileColIndexInfo, data_only=True)
sheetColIndexKeShi = bookColIndex['科室']
maxRowColIndexKeShi = sheetColIndexKeShi.max_row
maxColColIndexKeShi = sheetColIndexKeShi.max_column
logger.debug('数据列索引信息.xlsx 科室-------行数：{}行    列数：{}列'.format(maxRowColIndexKeShi, maxColColIndexKeShi))
listColKeshi = {}
for rowIndex in range(2, maxRowColIndexKeShi + 1):
    valueKeShiName = sheetColIndexKeShi.cell(rowIndex, 1).value
    valueKeShiIndex = sheetColIndexKeShi.cell(rowIndex, 2).value
    if valueKeShiName is not None and valueKeShiName != '':
        listColKeshi[valueKeShiName] = valueKeShiIndex
logger.debug('{}'.format(listColKeshi))

sheetColIndexSheQu = bookColIndex['社区村']
maxRowColIndexSheQu = sheetColIndexSheQu.max_row
maxColColIndexSheQu = sheetColIndexSheQu.max_column
logger.debug('数据列索引信息.xlsx 社区村-------行数：{}行    列数：{}列'.format(maxRowColIndexSheQu, maxColColIndexSheQu))
listColSheQu = {}
for rowIndex in range(2, maxRowColIndexSheQu + 1):
    valueSheQuName = sheetColIndexSheQu.cell(rowIndex, 1).value
    valueSheQuIndex = sheetColIndexSheQu.cell(rowIndex, 2).value
    if valueSheQuName is not None and valueSheQuName != '':
        listColSheQu[valueSheQuName] = valueSheQuIndex
logger.debug('{}'.format(listColSheQu))

# 打开 模板-月度得分统计.xlsx
# bookResultMuBan = openpyxl.load_workbook(fileResultMuBan, data_only=True)
bookResultMuBan = openpyxl.load_workbook(fileResultMuBan)
sheetResultKeShi = bookResultMuBan['科室']
maxRowResultMuBanKeShi = sheetResultKeShi.max_row
maxColResultMuBanKeShi = sheetResultKeShi.max_column
logger.debug('模板-月度得分统计.xlsx 科室-------行数：{}行    列数：{}列'.format(maxRowResultMuBanKeShi, maxColResultMuBanKeShi))

sheetResultSheQu = bookResultMuBan['社区村']
maxRowResultMuBanSheQu = sheetResultSheQu.max_row
maxColResultMuBanSheQu = sheetResultSheQu.max_column
logger.debug('模板-月度得分统计.xlsx 社区村-------行数：{}行    列数：{}列'.format(maxRowResultMuBanSheQu, maxColResultMuBanSheQu))


chengBan = ChengBanBuMen.ChengBanDirectory()
chengBan.load_cheng_ban_list(fileChengBanBuMen)
listChengBanKeShi = chengBan.get_list_ke_shi()
logger.debug('{}'.format(listChengBanKeShi))
listChengBan = {}
for index in listChengBanKeShi:
    bumenChengJi = BuMenChengJi.buMenChengJi(1)
    bumenChengJi.name = index
    bumenChengJi.fuZeRen = listChengBanKeShi[index].fuZeRen
    bumenChengJi.zhuGuanLingDao = listChengBanKeShi[index].zhuGuanLingDao
    bumenChengJi.baoPianLingDao = listChengBanKeShi[index].baoPianLingDao
    bumenChengJi.suoShuPianQu = listChengBanKeShi[index].suoShuPianQu
    listChengBan[index] = bumenChengJi

for index in listChengBan:
    logger.debug('{} : {}'.format(index, listChengBan[index].fuZeRen))

listChengBanSheQu = chengBan.get_list_she_qu()
logger.debug('{}'.format(listChengBanSheQu))
listSheQuInfo = {}
for index in listChengBanSheQu:
    bumenChengJi = BuMenChengJi.buMenChengJi(0)
    bumenChengJi.name = index
    bumenChengJi.fuZeRen = listChengBanSheQu[index].fuZeRen
    bumenChengJi.zhuGuanLingDao = listChengBanSheQu[index].zhuGuanLingDao
    bumenChengJi.baoPianLingDao = listChengBanSheQu[index].baoPianLingDao
    bumenChengJi.suoShuPianQu = listChengBanSheQu[index].suoShuPianQu
    bumenChengJi.qianRenSuQiuLiang = listChengBanSheQu[index].qianRenSuQiu
    bumenChengJi.fenDang = listChengBanSheQu[index].fenDang
    listChengBan[index] = bumenChengJi

for index in listChengBan:
    logger.debug('{} : {}'.format(index, listChengBan[index].fuZeRen))

sheetJieDan.insert_cols(1, 1)
sheetJieDan.cell(1, 1).value = '是否已处理'
colChengBanJieDan = hanchxl.get_col_index(sheetJieDan, '承办部门')

maxRowJieDan = sheetJieDan.max_row
logger.debug('maxRowJieDan ：{}'.format(maxRowJieDan))
numWeiChuLi = 0
for lineIndexJieDan in range(2, maxRowJieDan + 1):
    valueChengBanJieDan = '{}'.format(sheetJieDan.cell(lineIndexJieDan, colChengBanJieDan).value).strip()
    valueChengBanJieDan = valueChengBanJieDan.replace('(已删除)', '')
    logger.debug('接单 {} {}'.format(lineIndexJieDan, valueChengBanJieDan))
    if valueChengBanJieDan != '':
        if valueChengBanJieDan in listChengBan:
            listChengBan[valueChengBanJieDan].jieDanZongLiang = listChengBan[valueChengBanJieDan].jieDanZongLiang + 1
            if listChengBan[valueChengBanJieDan].isKeShi == 1:
                sheetJieDan.cell(lineIndexJieDan, 1).value = '已处理-科室'
            else:
                sheetJieDan.cell(lineIndexJieDan, 1).value = '已处理-社区村'
        else:
            logger.debug('接单  {} 不在承办部门列表'.format(valueChengBanJieDan))
            sheetJieDan.cell(lineIndexJieDan, 1).value = '未找到该承办部门'
            numWeiChuLi = numWeiChuLi + 1
    else:
        logger.debug('接单  {}没有承办部门或标签类型'.format(valueChengBanJieDan))
        sheetJieDan.cell(lineIndexJieDan, 1).value = '没有承办部门或标签类型'
        numWeiChuLi = numWeiChuLi + 1

for index in listChengBan:
    logger.debug('{} 接单量: {}'.format(index, listChengBan[index].jieDanZongLiang))

if numWeiChuLi > 0:
    # 保存未匹配承办部门的表格
    bookJieDan.save(fileJieDanNo)
    logger.debug('保存  {}'.format(fileJieDanNo))


sheetKaoHe.insert_cols(1, 1)
sheetKaoHe.cell(1, 1).value = '是否已处理'

BuMenChengJi.get_kao_he_info_by_ming_xi(listChengBan, sheetKaoHe)
listDeFen = {}
listAllNone = []            # 接单量 和 考核量 均为0
listOnlyJieDan = {}         # 考核量 为0
for index in listChengBan:
    logger.debug('{} 得分: {}'.format(index, listChengBan[index].deFen))
    listDeFen[index] = listChengBan[index].deFen
    if listChengBan[index].jieDanZongLiang == 0 and listChengBan[index].kaoHeZongLiang == 0:
        listAllNone.append(listChengBan[index].name)
    if listChengBan[index].jieDanZongLiang != 0 and listChengBan[index].kaoHeZongLiang == 0:
        listOnlyJieDan[listChengBan[index].name] = listChengBan[index].jieDanZongLiang

listDeFen = dict(sorted(listDeFen.items(), key=lambda kv: kv[1], reverse=True))
logger.debug('排序后 {}'.format(listDeFen))
for index in listDeFen:
    logger.debug('listDeFen 排序后 {}:{}'.format(index, listDeFen[index]))

listOnlyJieDan = dict(sorted(listOnlyJieDan.items(), key=lambda kv: kv[1], reverse=False))
for index in listOnlyJieDan:
    logger.debug('listOnlyJieDan 排序后 {}:{}'.format(index, listOnlyJieDan[index]))

logger.debug('listAllNone: {}'.format(listAllNone))

strTitleKeShi = '{}月考核期拱辰街道机关科室接诉即办排名情况\r\n（'.format(numMonth)
strTitleSheQu = '{}月考核期拱辰街道社区（村）接诉即办排名情况\r\n（'.format(numMonth)
if numMonth == 1:
    strTitleKeShi = strTitleKeShi + '{}年12月19日0时至'.format(numYear - 1)
    strTitleKeShi = strTitleKeShi + '{}年1月18日24时)'.format(numYear)
    strTitleSheQu = strTitleSheQu + '{}年12月17日0时至'.format(numYear - 1)
    strTitleSheQu = strTitleSheQu + '{}年1月18日24时)'.format(numYear)
else:
    strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(numYear, numMonth - 1)
    strTitleKeShi = strTitleKeShi + '{}月18日24时)'.format(numMonth)
    strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(numYear, numMonth - 1)
    strTitleSheQu = strTitleSheQu + '{}月18日24时)'.format(numMonth)

sheetResultKeShi.cell(1, 1).value = strTitleKeShi
sheetResultSheQu.cell(1, 1).value = strTitleSheQu


def inset_not_none_info(is_ke_shi, title_name, insert_row, insert_value):
    if insert_value is not None:
        if title_name in listColKeshi or title_name in listColSheQu:
            if is_ke_shi == 1:
                sheetResultKeShi.cell(insert_row, listColKeshi[title_name]).value = insert_value
            else:
                sheetResultSheQu.cell(insert_row, listColSheQu[title_name]).value = insert_value


lastDeFenKeshi = 0
lastPaimMingKeshi = 1
lastDefenSheQu = 0
lastPaimMingSheQu = 1
# 承办部门信息插入到结果表
def insert_into_excel(bu_men_info, insert_row):
    global lastDeFenKeshi
    global lastPaimMingKeshi
    global lastDefenSheQu
    global lastPaimMingSheQu

    if bu_men_info.isKeShi == 1:
        logger.debug('科室  {} {} {}'.format(bu_men_info.name, insert_row, listColKeshi['接单部门']))
    else:
        logger.debug('社区村 {} {} {}'.format(bu_men_info.name, insert_row, listColKeshi['接单部门']))
    # inset_not_none_info(1, '接单部门', insert_row, bu_men_info.name)
    inset_not_none_info(bu_men_info.isKeShi, '接单部门', insert_row, bu_men_info.name)
    if bu_men_info.jieDanZongLiang > 0:
        inset_not_none_info(bu_men_info.isKeShi, '接单总量', insert_row, bu_men_info.jieDanZongLiang)
    if bu_men_info.kaoHeZongLiang > 0:
        inset_not_none_info(bu_men_info.isKeShi, '考核总量', insert_row, bu_men_info.kaoHeZongLiang)
    if bu_men_info.onlyKaoHeLianXi > 0:
        inset_not_none_info(bu_men_info.isKeShi, '仅考核响应率-考核', insert_row, bu_men_info.onlyKaoHeLianXi)
        inset_not_none_info(bu_men_info.isKeShi, '仅考核响应率-联系', insert_row, bu_men_info.onlyLianXi)
        inset_not_none_info(bu_men_info.isKeShi, '仅考核响应率-响应率', insert_row, bu_men_info.onlyLvLianXi)
    if bu_men_info.sanKaoHeQu > 0:
        inset_not_none_info(bu_men_info.isKeShi, '区派三率-考核', insert_row, bu_men_info.sanKaoHeQu)
        inset_not_none_info(bu_men_info.isKeShi, '区派三率-联系', insert_row, bu_men_info.sanLianXiQu)
        inset_not_none_info(bu_men_info.isKeShi, '区派三率-解决', insert_row, bu_men_info.sanJieJueQu)
        inset_not_none_info(bu_men_info.isKeShi, '区派三率-满意', insert_row, bu_men_info.sanManYiQu)
        inset_not_none_info(bu_men_info.isKeShi, '区派三率-基本满意', insert_row, bu_men_info.sanJiBenManYiQu)
        inset_not_none_info(bu_men_info.isKeShi, '区派三率-响应率', insert_row, bu_men_info.sanLvLianXiQu)
        inset_not_none_info(bu_men_info.isKeShi, '区派三率-解决率', insert_row, bu_men_info.sanLvJieJueQu)
        inset_not_none_info(bu_men_info.isKeShi, '区派三率-满意率', insert_row, bu_men_info.sanLvManYiQu)
    if bu_men_info.sanKaoHeShi > 0:
        inset_not_none_info(bu_men_info.isKeShi, '市派三率-考核', insert_row, bu_men_info.sanKaoHeShi)
        inset_not_none_info(bu_men_info.isKeShi, '市派三率-联系', insert_row, bu_men_info.sanLianXiShi)
        inset_not_none_info(bu_men_info.isKeShi, '市派三率-解决', insert_row, bu_men_info.sanJieJueShi)
        inset_not_none_info(bu_men_info.isKeShi, '市派三率-满意', insert_row, bu_men_info.sanManYiShi)
        inset_not_none_info(bu_men_info.isKeShi, '市派三率-基本满意', insert_row, bu_men_info.sanJiBenManYiShi)
        inset_not_none_info(bu_men_info.isKeShi, '市派三率-响应率', insert_row, bu_men_info.sanLvLianXiShi)
        inset_not_none_info(bu_men_info.isKeShi, '市派三率-解决率', insert_row, bu_men_info.sanLvJieJueShi)
        inset_not_none_info(bu_men_info.isKeShi, '市派三率-满意率', insert_row, bu_men_info.sanLvManYiShi)
    inset_not_none_info(bu_men_info.isKeShi, '平均响应率', insert_row, bu_men_info.avgLvLianXi)
    inset_not_none_info(bu_men_info.isKeShi, '平均解决率', insert_row, bu_men_info.avgLvJieJue)
    inset_not_none_info(bu_men_info.isKeShi, '平均满意率', insert_row, bu_men_info.avgLvManYi)
    inset_not_none_info(bu_men_info.isKeShi, '得分', insert_row, bu_men_info.deFen)

    if bu_men_info.isKeShi == 1:
        if bu_men_info.deFen == lastDeFenKeshi:
            inset_not_none_info(bu_men_info.isKeShi, '排名', insert_row, lastPaimMingKeshi)
        else:
            lastPaimMingKeshi = insert_row - 3
            inset_not_none_info(bu_men_info.isKeShi, '排名', insert_row, lastPaimMingKeshi)
        lastDeFenKeshi = bu_men_info.deFen
        inset_not_none_info(bu_men_info.isKeShi, '主管领导', insert_row, bu_men_info.zhuGuanLingDao)
    else:
        inset_not_none_info(bu_men_info.isKeShi, '千人诉求量', insert_row, bu_men_info.qianRenSuQiuLiang)
        inset_not_none_info(bu_men_info.isKeShi, '所属片区', insert_row, bu_men_info.suoShuPianQu)
        inset_not_none_info(bu_men_info.isKeShi, '分档', insert_row, bu_men_info.fenDang)


logger.debug("listAllNone：{}".format(listAllNone))
logger.debug("listOnlyJieDan：{}".format(listOnlyJieDan))
logger.debug("listDeFen：{}".format(listDeFen))
insetKeShi = 4
insertSheQu = 4
for index in listDeFen:
    if index in listAllNone:
        logger.debug("保存未接单未考核的部门 {}".format(index))
        if listChengBan[index].isKeShi == 1:
            insert_into_excel(listChengBan[index], insetKeShi)
            insetKeShi = insetKeShi + 1
        else:
            insert_into_excel(listChengBan[index], insertSheQu)
            insertSheQu = insertSheQu + 1

for index in listDeFen:
    if index in listOnlyJieDan:
        logger.debug("保存只接单的部门{}".format(index))
        if listChengBan[index].isKeShi == 1:
            insert_into_excel(listChengBan[index], insetKeShi)
            insetKeShi = insetKeShi + 1
        else:
            insert_into_excel(listChengBan[index], insertSheQu)
            insertSheQu = insertSheQu + 1

for index in listDeFen:
    logger.debug("保存考核的部门 {}".format(index))
    if index not in listAllNone and index not in listOnlyJieDan:
        logger.debug("{}".format(index))
        if listChengBan[index].isKeShi == 1:
            insert_into_excel(listChengBan[index], insetKeShi)
            insetKeShi = insetKeShi + 1
        else:
            insert_into_excel(listChengBan[index], insertSheQu)
            insertSheQu = insertSheQu + 1

bookResultMuBan.save(fileResultSave)




